/**
 * 
 */
package be;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;



/**
 * 
 *
 */
public class StoredProcedures implements backend {

	public Connection conn;
	
	public StoredProcedures(){
		
	}
	/** 
	 * The database ip and name has to be coded directly into the class since we're using DriverManager instead of context lookup.
	 * Since we're using stored procedures the user needs to have the authority to make them in the database. Change with:
	 * GRANT CREATE ROUTINE ON mydatabase.* to 'user'@'%';
	 * GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
	 * or simply
	 * GRANT ALL ON mydatabase.* to 'theuser'@'%';
	 * need admin for the machine to be able to do this.
	 */
	public void connect() {
		Properties prop = new Properties();
		prop.put("user", "yuuki");
		prop.put("password", "orka");
		try {
			conn = DriverManager.getConnection("jdbc:mysql://130.229.128.198/test", prop);
		} catch (Exception e) {
			System.out.println("ERROOOOORRR!!! Connect!!");
			e.printStackTrace();
		}
	}

	/** 
	 * WORKS
	 */
	public void disconnect() {
		try{
			conn.close();
		}catch (Exception e){
			System.out.println("well somehow the code seems to cast an exception when disconnecing..");
			e.printStackTrace();
		}
	}

	/** 
	 * WORKS
	 * checking if a procedure exists, if not it will create one and run it. 
	 */
	public void CreateTable(String tableName, Table variables) {
		connect();
		try{
			if(conn == null || conn.isClosed()){
				System.out.println("the connection is closed or null");
			}
			else{
				Statement s = conn.createStatement();
				ResultSet r = s.executeQuery("SELECT * FROM information_schema.routines where specific_name = 'CREATE_TABLE_"+tableName.toUpperCase()+"';");
				if(!r.next()){
					String proc = 	"create procedure CREATE_TABLE_"+tableName.toUpperCase()+"() " +
					"begin " +
						"CREATE table "+tableName+" ("+ variables.getVars() +");"+
					"end";
					System.out.println("DEBUG_QUERY: "+proc);
					s.clearBatch();
					s.execute(proc);
				}
				System.out.println("DEBUG: at calling the procedure");
				s.clearBatch();
				String call = "{call CREATE_TABLE_"+tableName.toUpperCase()+"()}";
				s.execute(call);
			
			}
		}catch(Exception e){
			System.out.println("Since I'm lazy as fk idk which error it is but.. anyway create table seems to fail.");
			e.printStackTrace();
		}
		disconnect();
	}

	/**
	 * WORKS
	 * This can actually break quite badly if we got two inserts with the same amount of same sort of params 
	 * but with different columns as targets. e.g. table with 2 ints and 1 varchar first insert is to the first int and the varchar
	 * and the second is the the 2nd int and the varchar. This method will still use the same sp for both calls. 
	 * I could create a new one for every single use but...
	 */
	public int Insert(String tableName, InsertList values) {
		connect();
		try{
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT * FROM information_schema.routines where specific_name = 'INSERT_"+tableName.toUpperCase() +"_"+values.spEndTag()+"';");
			if(!r.next()){
				String proc =
						"CREATE PROCEDURE INSERT_"+tableName.toUpperCase()+"_"+values.spEndTag() +"(" +
						values.generateSPParam()+
						")" +
						"begin " +
							"INSERT INTO "+ tableName + " " +
							values.spInsert()
						+"end";
				System.out.println("PROC: " +proc);
				s.execute(proc);
				
			}
			s.clearBatch();
			String query = "{call INSERT_"+tableName.toUpperCase() +"_"+values.spEndTag()+"(" + 
			// need code for insert values here in the right order..
			values.spValues()+
			")}";
			s.execute(query);
		}
		catch(SQLException e){
			disconnect();
			System.out.println("SQL statement error! INSERT");
			e.printStackTrace();
		}
		disconnect();
		return -1;
	}

	/** 
	 * TODO: NEEDS TESTING AKA TRY TO FETCH THE BLOB
	 * Works, aka something actually reaches the database.
	 */
	public void storeBLOB(String tableName, FileInputStream file) {
		connect();
		try {
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT * FROM information_schema.routines where specific_name = 'STORE_BLOB_"+tableName.toUpperCase()+"';");
			if(!r.next()){
				//no stored procedure, we need to make one.
				String proc = 	"CREATE PROCEDURE STORE_BLOB_"+tableName.toUpperCase()+"("+
								"IN inb BLOB"+
								")" +
								"begin " +
									"INSERT INTO "+tableName+" VALUES (inb);"+
								"end;"
								;
				s.clearBatch();
				System.out.println("PROC:"+ proc);
				s.execute(proc);
			}
			s.clearBatch();
			
			CallableStatement c = conn.prepareCall("{call STORE_BLOB_"+tableName.toUpperCase()+"(?)}");
			c.setBinaryStream(1,file);
			c.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		disconnect();
	}

	/** 
	 * DONE WORKS
	 */
	public ResultSet getData(String table, InsertList select, InsertList where, String tail) {
		connect();
		String t = tail;
		ResultSet r = null;
		if(!tail.equals("")){
			t = "T";
		}
		try {
			Statement s = conn.createStatement();
			
			r = s.executeQuery("SELECT * FROM information_schema.routines where specific_name = 'GET_DATA_FROM_"+table.toUpperCase()+"_"+where.spEndTag()+"_"+select.spEndTag()+"_"+t+"';");
			if(!r.next()){
				//no stored procedure, we need to make one.
				String proc = 	"CREATE PROCEDURE GET_DATA_FROM_"+table.toUpperCase()+"_"+where.spEndTag()+"_"+select.spEndTag()+"_"+ t + "(" + 
								where.generateSPParam() +
								")" +
								"begin " +
									"SELECT "+select.spSelectCall()+" FROM " + table +" WHERE " + where.spCompare() +" "+tail+";"+
								"end;"
								;
				s.clearBatch();
				System.out.println("PROC: "+proc);
				s.execute(proc);
			}
			s.clearBatch();

			String call = "{call GET_DATA_FROM_"+table.toUpperCase()+"_"+where.spEndTag()+"_"+select.spEndTag()+"_"+t+"("
					 + where.spValues()+
			")}";
			System.out.println("CALL: "+call);
			r = s.executeQuery(call);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//disconnect();
		return r;
	}

	/**
	 * 	WORKS 
	 */
	public void deleteData(String table,InsertList d) {
		// TODO Auto-generated method stub
		connect();
		try {
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT * FROM information_schema.routines where specific_name = 'DELETE_"+table.toUpperCase()+"_"+d.spEndTag()+"';");
			if(!r.next()){
				//no stored procedure, we need to make one.
				String proc = 	"CREATE PROCEDURE DELETE_"+table.toUpperCase()+"_"+d.spEndTag() + "(" + 
								d.generateSPParam()+
								")" +
								"begin " +
									"DELETE FROM " + table +" WHERE " + d.spUpdate()+ 
								";end;"
								;
				s.clearBatch();
				System.out.println("DELETE QUERY: " + proc);
				s.execute(proc);
			}
			s.clearBatch();
			
			s.execute("{call DELETE_"+table.toUpperCase()+"_"+d.spEndTag()+"(" +
					d.spValues()+
					")}");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		disconnect();
	}

	/** 
	 * WORKS
	 */
	public void UpdateData(String table, InsertList values,InsertList w) {
		connect();
		try {
			Statement s = conn.createStatement();
			ResultSet r = s.executeQuery("SELECT * FROM information_schema.routines where specific_name = 'UPDATE_"+table.toUpperCase()+"_"+values.spEndTag()+"';");
			if(!r.next()){
				//no stored procedure, we need to make one.
				String proc = 	"CREATE PROCEDURE UPDATE_"+table.toUpperCase()+"_"+values.spEndTag() + "(" + 
								values.generateSPParam()+","+
								w.selSPParam()+
								")" +
								"begin " +
									"UPDATE " + table + " SET " + values.spUpdate()+
									" WHERE " + w.spSelect()+
								";end;"
								;
				s.clearBatch();
				System.out.println("UPDATE QUERY: " + proc);
				s.execute(proc);
			}
			s.clearBatch();
			
			s.execute("{call UPDATE_"+table.toUpperCase()+"_"+values.spEndTag()+"(" +
					values.spValues()+","+w.spValues()+
					")}");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		disconnect();

	}

}
